<?php
class Tourism_order_model extends CI_Model
{
    private $table   = 'tourism_order';
    private $table_2 = 'tourism_order_product';
    private $table_3 = 'tourism_order_product_profit';
    private $table_4 = 'user';
    private $table_5 = 'user_type';
    private $table_6 = 'tourism_order_refund';
    private $table_7 = 'tourism_delivery_address';
    private $table_8 = 'user_detail';
    /**
     * 符合搜索条件数据的总数量
     * @param unknown $params
     */
    public function total($params = array())
    {
        $this->db->from($this->table.' AS tourism_order');
        $this->db->join($this->table_4.' AS user', 'user.uid = tourism_order.seller_uid');
        $this->db->join($this->table_8.' AS user_detail', 'user_detail.uid = tourism_order.seller_uid');
        $this->db->join($this->table_2.' AS tourism_order_product','tourism_order.order_id=tourism_order_product.order_id');
         //$this->db->join($this->table_7.' AS tourism_delivery_address','tourism_delivery_address.address_id=tourism_order.address_id');
        if (!empty($params['order_id'])) {
            $this->db->where('tourism_order.order_id', $params['order_id']);
        }
        if (!empty($params['order_sn'])) {
            $this->db->where('tourism_order.order_sn', $params['order_sn']);
        }
        if (!empty($params['seller_alias_name'])) {
            $this->db->where("((`user`.`user_name` LIKE '%{$params['seller_alias_name']}%') OR (`user`.`alias_name` LIKE '%{$params['seller_alias_name']}%'))");
        }
        if (!empty($params['payer_uid'])) {
            $this->db->where("((`tourism_order`.`payer_uid` LIKE '%{$params['payer_uid']}%') OR (`tourism_order`.`user_name` LIKE '%{$params['payer_uid']}%'))");
        }
        if (!empty($params['order_state'])) {
            $this->db->where('tourism_order.order_state', $params['order_state']);
        }
        if (!empty($params['order_status'])) {
            $this->db->where('tourism_order.order_status', $params['order_status']);
        }
        if (!empty($params['goods_name'])){
            $this->db->like('tourism_order_product.goods_name', $params['goods_name']);
        }
        if (!empty($params['receive_phone'])){
            $this->db->like('tourism_order.detail', $params['receive_phone']);
        }
        if (!empty($params['order_note'])){
            $this->db->like('tourism_order.order_note', $params['order_note']);
        }
        if (!empty($params['start_date'])) {
            $this->db->where(array('tourism_order.created_at >' => $params['start_date'].' 00:00:00'));
        }
        if (!empty($params['end_date'])) {
            $this->db->where(array('tourism_order.created_at <=' => $params['end_date'].' 23:59:59'));
        }
	    $this->db->where('tourism_order.is_delete','1');
        return $this->db->count_all_results();
    }

    /**
     * 符合搜索条件数据 默认取20条
     * @param unknown $page_num
     * @param unknown $num
     * @param unknown $params
     */
    public function page_list($page_num, $num, $params = array())
    {
        $this->db->select('tourism_order.*, user.user_name AS provider_name,user.alias_name,user.mobile_phone,user_detail.cellphone');
        $this->db->from($this->table.' AS tourism_order');
        $this->db->join($this->table_4.' AS user', 'user.uid = tourism_order.seller_uid');
        $this->db->join($this->table_8.' AS user_detail', 'user_detail.uid = tourism_order.seller_uid');
        $this->db->join($this->table_2.' AS tourism_order_product','tourism_order.order_id=tourism_order_product.order_id');
         //$this->db->join($this->table_7.' AS tourism_delivery_address','tourism_delivery_address.address_id=tourism_order.address_id');
        if (!empty($params['order_id'])) {
            $this->db->where('tourism_order.order_id', $params['order_id']);
        }
        if (!empty($params['order_sn'])) {
            $this->db->where('tourism_order.order_sn', $params['order_sn']);
        }
        if (!empty($params['seller_alias_name'])) {
            $this->db->where("((`user`.`user_name` LIKE '%{$params['seller_alias_name']}%') OR (`user`.`alias_name` LIKE '%{$params['seller_alias_name']}%'))");
        }
        if (!empty($params['payer_uid'])) {
            $this->db->where("((`tourism_order`.`payer_uid` LIKE '%{$params['payer_uid']}%') OR (`tourism_order`.`user_name` LIKE '%{$params['payer_uid']}%'))");
        }
        if (!empty($params['order_state'])) {
            $this->db->where('tourism_order.order_state', $params['order_state']);
        }
        if (!empty($params['order_status'])) {
            $this->db->where('tourism_order.order_status', $params['order_status']);
        }
        if (!empty($params['goods_name'])){
            $this->db->like('tourism_order_product.goods_name', $params['goods_name']);
        }
        if (!empty($params['receive_phone'])){
            $this->db->like('tourism_order.detail', $params['receive_phone']);
        }
        if (!empty($params['order_note'])){
            $this->db->like('tourism_order.order_note', $params['order_note']);
        }
        if (!empty($params['start_date'])) {
            $this->db->where(array('tourism_order.created_at >' => $params['start_date'].' 00:00:00'));
        }
        if (!empty($params['end_date'])) {
            $this->db->where(array('tourism_order.created_at <=' => $params['end_date'].' 23:59:59'));
        }
        $this->db->where('tourism_order.is_delete','1');
        $this->db->group_by('tourism_order.order_id');
        $this->db->order_by('tourism_order.order_id', 'DESC');
        $this->db->limit($page_num, $num);
        
        return $this->db->get();
    }

    /**
     * 获取金额统计信息
     * @param unknown $params
     */
    public function order_list_sum($params = array())
    {
        $this->db->select_sum('tourism_order.actual_price');
        $this->db->select_sum('tourism_order.order_price');
        $this->db->select_sum('tourism_order.transport_cost');
        $this->db->select_sum('tourism_order.order_pay_total');
        $this->db->select_sum('tourism_order.pay_swim');
        $this->db->from($this->table.' AS tourism_order');
        $this->db->join($this->table_2.' AS tourism_order_product','tourism_order.order_id=tourism_order_product.order_id');
        $this->db->join($this->table_4.' AS user', 'user.uid = tourism_order.seller_uid');
        if (!empty($params['order_id'])) {
            $this->db->where('tourism_order.order_id', $params['order_id']);
        }
        if (!empty($params['order_sn'])) {
            $this->db->where('tourism_order.order_sn', $params['order_sn']);
        }
        if (!empty($params['seller_alias_name'])) {
            $this->db->where("((`user`.`user_name` LIKE '%{$params['seller_alias_name']}%') OR (`user`.`alias_name` LIKE '%{$params['seller_alias_name']}%'))");
        }
        if (!empty($params['payer_uid'])) {
            $this->db->where("((`tourism_order`.`payer_uid` LIKE '%{$params['payer_uid']}%') OR (`tourism_order`.`user_name` LIKE '%{$params['payer_uid']}%'))");
        }
        if (!empty($params['order_state'])) {
            $this->db->where('tourism_order.order_state', $params['order_state']);
        }
        if (!empty($params['order_status'])) {
            $this->db->where('tourism_order.order_status', $params['order_status']);
        }
        if (!empty($params['goods_name'])){
            $this->db->like('tourism_order_product.goods_name', $params['goods_name']);
        }
        if (!empty($params['receive_phone'])){
            $this->db->like('tourism_order.detail', $params['receive_phone']);
        }
        if (!empty($params['order_note'])){
            $this->db->like('tourism_order.order_note', $params['order_note']);
        }
        if (!empty($params['start_date'])) {
            $this->db->where(array('tourism_order.created_at >' => $params['start_date'].' 00:00:00'));
        }
        if (!empty($params['end_date'])) {
            $this->db->where(array('tourism_order.created_at <=' => $params['end_date'].' 23:59:59'));
        }
        return $this->db->get();
    }
    
    public function findByOrderId($order_id)
    {
        $this->db->where('order_id', $order_id);
        return $this->db->get($this->table);
    }

    /**
     * 获取分润数组
     * @param unknown $orderId
     */
    public function getProfitByOrderId($orderId)
    {
        $this->db->select('`tourism_order_product_profit`.*, `user`.`uid`, `user`.`user_name`, `user`.`alias_name`, `user_type`.`type_name`');
        $this->db->from($this->table_3 . ' AS tourism_order_product_profit');
        $this->db->join($this->table_2 . ' AS tourism_order_product', 'tourism_order_product.order_product_id = tourism_order_product_profit.order_product_id');
        $this->db->join($this->table_4 . ' AS user', 'user.uid = tourism_order_product_profit.uid');
        $this->db->join($this->table_5 . ' AS user_type', 'user_type.type_id = tourism_order_product_profit.bit_trade');
        $this->db->where('tourism_order_product.order_id', $orderId);
        return $this->db->get();
    }
    
    /**
     * 修改运费
     * @param unknown $order_id
     * @param unknown $transport_cost
     */
    public function modifyTransport($order_id, $transport_cost)
    {
        $data = array(
            'transport_cost' => $transport_cost
        );
        $this->db->where('order_id', $order_id);
        return $this->db->update($this->table, $data);
    }
    
     /**
     * 修改订单备注--着想员工自己插入
     * @param unknown $order_id
     * @param unknown $transport_cost
     */
    public function modifyOrderNote($order_id,$order_note)
    {
        $data = array(
                'order_note' => $order_note
        );
        $this->db->where('order_id',$order_id);
        return $this->db->update($this->table, $data);
    }
    
    
    /*商品今日订单统计**/
    public function tourism_today_order()
    {
        $this->db->where_in('order_state', array('2', '3', '4'));
        $this->db->where_in('order_status', array('3', '4', '5', '6'));
        $this->db->where('created_at >=', date('Y-m-d 00:00:00'));
        $this->db->where('created_at <=', date('Y-m-d 23:59:59'));
        return $this->db->count_all_results($this->table);
    }

    /*商品昨日订单统计**/
    public function tourism_yesterday_order()
    {
        $this->db->where_in('order_state', array('2', '3', '4'));
        $this->db->where_in('order_status', array('3', '4', '5', '6'));
        $this->db->where('created_at >=', date('Y-m-d 00:00:00', strtotime('-1 day')));
        $this->db->where('created_at <=', date('Y-m-d 23:59:59', strtotime('-1 day')));
        return $this->db->count_all_results($this->table);
    }

    /*商品历时订单统计**/
    public function tourism_all_order()
    {
        $this->db->where_in('order_state', array('2', '3', '4'));
        $this->db->where_in('order_status', array('3', '4', '5', '6'));
        $this->db->where('created_at <=', date('Y-m-d 23:59:59'));
        return $this->db->count_all_results($this->table);
    }
    
    /**
     * 商品销售额银行统计
     */
    public function tourismSalesBankTotal($search_param=array())
    {
        $this->db->select('tourism_order.pay_bank');
        $this->db->select_sum('tourism_order.order_pay_total');
        $this->db->select_sum('tourism_order.transport_cost');
        //$this->db->select_sum('tourism_order_product.pay_amount * (tourism_order_product.number - tourism_order_product.refund_num)', 'order_refund_total');
        $this->db->from($this->table.' AS tourism_order');
        if (!empty($search_param['start_date'])) {
            $this->db->where(array('tourism_order.created_at >' => $search_param['start_date']));
        }
        if (!empty($search_param['end_date'])) {
            $this->db->where(array('tourism_order.created_at <=' => $search_param['end_date']));
        }
        $this->db->where_in('tourism_order.order_status', array('3', '4', '5', '6'));
        $this->db->where('tourism_order.payment_type !=', 2); //不是到付
        $this->db->where('tourism_order.pay_bank >', 0);
        $this->db->group_by('tourism_order.pay_bank');
        $this->db->order_by('SUM(`tourism_order`.`order_pay_total`)', 'DESC');
        return $this->db->get();
    }
    
    /**
     * 商品销售额银行退款统计
     */
    public function tourismSalesBankRefundTotal($search_param=array(), $isArray=false)
    {
        $this->db->select('tourism_order.pay_bank');
        $this->db->select_sum('tourism_order_product.pay_amount * (tourism_order_product.number - tourism_order_product.refund_num)', 'order_refund_total');
        $this->db->select_sum('tourism_order_refund.counter_fee', 'order_refund_fee');
        $this->db->from($this->table.' AS tourism_order');
        $this->db->join($this->table_2 . ' AS tourism_order_product', 'tourism_order_product.order_id = tourism_order.order_id');
        $this->db->join($this->table_6 . ' AS tourism_order_refund', 'tourism_order_refund.order_product_id = tourism_order_product.order_product_id');
        if (!empty($search_param['start_date'])) {
            $this->db->where(array('tourism_order.created_at >' => $search_param['start_date']));
        }
        if (!empty($search_param['end_date'])) {
            $this->db->where(array('tourism_order.created_at <=' => $search_param['end_date']));
        }
        $this->db->where_in('tourism_order.order_status', array('3', '4', '5', '6'));
        $this->db->where('tourism_order.payment_type !=', 2); //不是到付
        $this->db->where('tourism_order.pay_bank >', 0);
        $this->db->where('tourism_order_refund.flag', 2); //已经退款
        $this->db->group_by('tourism_order.pay_bank');
        $result = $this->db->get();
        if ($isArray) {
            $rows = array();
            foreach ($result->result_array() as $row) {
                $rows[$row['pay_bank']] = $row;
            }
            return $rows;
        }
        return $result;
    }
    
    /**
     * 商品销售额退款统计
     */
    public function tourismSalesRefundTotal($search_param=array())
    {
        $this->db->select_sum('tourism_order_product.pay_amount * (tourism_order_product.number - tourism_order_product.refund_num)', 'order_refund_total');
        $this->db->select_sum('tourism_order_refund.counter_fee', 'order_refund_fee');
        $this->db->from($this->table.' AS tourism_order');
        $this->db->join($this->table_2 . ' AS tourism_order_product', 'tourism_order_product.order_id = tourism_order.order_id');
        $this->db->join($this->table_6 . ' AS tourism_order_refund', 'tourism_order_refund.order_product_id = tourism_order_product.order_product_id');
        if (!empty($search_param['start_date'])) {
            $this->db->where(array('tourism_order.created_at >' => $search_param['start_date']));
        }
        if (!empty($search_param['end_date'])) {
            $this->db->where(array('tourism_order.created_at <=' => $search_param['end_date']));
        }
        $this->db->where_in('tourism_order.order_status', array('3', '4', '5', '6'));
        $this->db->where('tourism_order.payment_type !=', 2); //不是到付
        $this->db->where('tourism_order.pay_bank >', 0);
        $this->db->where('tourism_order_refund.flag', 2); //已经退款
        return $this->db->get();
    }
    
    /**
     * 商品销售额总统计
     */
    public function tourismSalesCountTotal($search_param=array())
    {
        $this->db->select_sum('tourism_order.order_pay_total');
        $this->db->select_sum('tourism_order.transport_cost');
        $this->db->from($this->table.' As tourism_order');
        if (!empty($search_param['start_date'])) {
            $this->db->where(array('tourism_order.created_at >' => $search_param['start_date']));
        }
        if (!empty($search_param['end_date'])) {
            $this->db->where(array('tourism_order.created_at <=' => $search_param['end_date']));
        }
        $this->db->where_in('tourism_order.order_status', array('3', '4', '5', '6'));
        $this->db->where('tourism_order.payment_type !=', 2); //不是到付
        $this->db->where('tourism_order.pay_bank >', 0);
        return $this->db->get();
    }
    
    /**
     * 商品用户销售统计
     */
    public function tourismUserSellerTotal($search_param=array())
    {
        $this->db->select('count(*)');
        $this->db->from($this->table.' As tourism_order');
        $this->db->join($this->table_4.' AS user', 'user.uid = tourism_order.payer_uid');
        if (!empty($search_param['payer_uid'])) {
            $this->db->where('tourism_order.payer_uid', $search_param['payer_uid']);
        }
        if (!empty($search_param['pay_bank'])) {
            $this->db->where('tourism_order.pay_bank', $search_param['pay_bank']);
        }
        if (!empty($search_param['start_date'])) {
            $this->db->where(array('tourism_order.created_at >' => $search_param['start_date']));
        }
        if (!empty($search_param['end_date'])) {
            $this->db->where(array('tourism_order.created_at <=' => $search_param['end_date']));
        }
        $this->db->where_in('tourism_order.order_status', array('3', '4', '5', '6'));
        $this->db->where('tourism_order.payment_type !=', 2); //不是到付
        $this->db->where('tourism_order.pay_bank >', 0);
        $this->db->group_by('tourism_order.payer_uid');
        return $this->db->get();
    }
    
    /**
     * 商品用户销售统计
     */
    public function tourismUserSellerList($page_num, $num, $search_param=array())
    {
        $this->db->select_sum('tourism_order.order_pay_total');
        $this->db->select_sum('tourism_order.transport_cost');
        $this->db->select('user.uid, user.user_name, user.alias_name, user.user_type, tourism_order.pay_bank');
        $this->db->from($this->table.' As tourism_order');
        $this->db->join($this->table_4.' AS user', 'user.uid = tourism_order.payer_uid');
        //$this->db->join($this->table_2.' AS tourism_order_product', 'tourism_order_product.order_id = tourism_order.order_id');
        if (!empty($search_param['payer_uid'])) {
            $this->db->where('tourism_order.payer_uid', $search_param['payer_uid']);
        }
        if (!empty($search_param['pay_bank'])) {
            $this->db->where('tourism_order.pay_bank', $search_param['pay_bank']);
        }
        if (!empty($search_param['start_date'])) {
            $this->db->where(array('tourism_order.created_at >' => $search_param['start_date']));
        }
        if (!empty($search_param['end_date'])) {
            $this->db->where(array('tourism_order.created_at <=' => $search_param['end_date']));
        }
        $this->db->where_in('tourism_order.order_status', array('3', '4', '5', '6'));
        $this->db->where('tourism_order.payment_type !=', 2); //不是到付
        $this->db->where('tourism_order.pay_bank >', 0);
        $this->db->group_by('tourism_order.payer_uid');
        $this->db->order_by('SUM(`tourism_order`.`order_pay_total`)', 'DESC');
        $this->db->limit($page_num, $num);
        return $this->db->get();
    }

    /**
     * @descripte
     * @Author xiumao
     * @date 2016/6/13 0013 下午 4:39
     */
    public function excelExport($params = array())
    {
        $this->db->select('tourism_order.order_sn,tourism_order.payer_uid,tourism_order.message,tourism_order.order_status,tourism_order.order_note,tourism_order_product.order_product_id,tourism_order_product.order_id,tourism_order_product.goods_attr_id,tourism_order_product.goods_name,
                            tourism_order_product.attribute_value,tourism_order_product.number,tourism_order_product.refund_num,tourism_order_product.barter_num,
                            tourism_order_product.market_price,tourism_order_product.shop_price,tourism_order_product.supply_price,tourism_order_product.pay_amount,
                            tourism_order_product.created_at,tourism_order_product.updated_at,user.user_name');
        $this->db->from($this->table.' AS tourism_order');
        $this->db->join($this->table_4.' AS user', 'user.uid = tourism_order.seller_uid');
        $this->db->join($this->table_8.' AS user_detail', 'user_detail.uid = tourism_order.seller_uid');
        $this->db->join($this->table_2.' AS tourism_order_product','tourism_order.order_id=tourism_order_product.order_id');
        // $this->db->join($this->table_7.' AS tourism_delivery_address','tourism_delivery_address.address_id=tourism_order.address_id');
        if (!empty($params['order_id'])) {
            $this->db->where('tourism_order.order_id', $params['order_id']);
        }
        if (!empty($params['order_sn'])) {
            $this->db->where('tourism_order.order_sn', $params['order_sn']);
        }
        if (!empty($params['seller_alias_name'])) {
            $this->db->where("((`user`.`user_name` LIKE '%{$params['seller_alias_name']}%') OR (`user`.`alias_name` LIKE '%{$params['seller_alias_name']}%'))");
        }
        if (!empty($params['payer_uid'])) {
            $this->db->where("((`tourism_order`.`payer_uid` LIKE '%{$params['payer_uid']}%') OR (`tourism_order`.`user_name` LIKE '%{$params['payer_uid']}%'))");
        }
        if (!empty($params['order_state'])) {
            $this->db->where('tourism_order.order_state', $params['order_state']);
        }
        if (!empty($params['order_status'])) {
            $this->db->where('tourism_order.order_status', $params['order_status']);
        }
        if (!empty($params['goods_name'])){
            $this->db->like('tourism_order_product.goods_name', $params['goods_name']);
        }
        if (!empty($params['receive_phone'])){
            $this->db->like('tourism_delivery_address.tel', $params['receive_phone']);
        }
        if (!empty($params['order_note'])){
            $this->db->like('tourism_order.order_note', $params['order_note']);
        }
        if (!empty($params['start_date'])) {
            $this->db->where(array('tourism_order.created_at >' => $params['start_date'].' 00:00:00'));
        }
        if (!empty($params['end_date'])) {
            $this->db->where(array('tourism_order.created_at <=' => $params['end_date'].' 23:59:59'));
        }
        $this->db->group_by('tourism_order.order_id');
        $this->db->order_by('tourism_order.order_id', 'DESC');
        return $this->db->get();
    }

    /**
     * @descripte   发货提醒  获取订单信息 供应商用户名 手机号
     * @Author xiumao
     * @date 2016/6/14 0014 上午 10:20
     */
    public function findUserPhoneByOrderId($orderId)
    {
        $this->db->select('tourism_order.order_id,tourism_order.order_sn,tourism_order.order_state,tourism_order.order_status,tourism_order.pay_time,user.user_name,user_detail.cellphone,user.mobile_phone');
        $this->db->from($this->table . ' AS tourism_order');
        $this->db->join($this->table_4 . ' AS user', 'tourism_order.seller_uid = user.uid');
        $this->db->join($this->table_8 . ' AS user_detail', 'user.uid = user_detail.uid');
        $this->db->where('tourism_order.order_id', $orderId);
        return $this->db->get();
    }

}